package com;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import javaBean.students_javaBean;

public class students_dao {

	public Collection<students_javaBean> getAll() {
		Collection<students_javaBean> stuVector = new ArrayList<students_javaBean>();
		Connection conn = ConnectionPool.getConn();
		try {
			if (conn != null) {
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery("select * from students");
				while (rs.next()) {
					students_javaBean stu = new students_javaBean();
					stu.setId(rs.getString("id"));
					stu.setName(rs.getString("name"));
					stu.setSex(rs.getString("sex"));
					stu.setAddress(rs.getString("address"));
					stu.setBirth(rs.getString("birth"));
					stuVector.add(stu);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return stuVector;
	}

	public static students_javaBean selectBy(String id) {
		students_javaBean stu = null;
		Statement stmt = null;
		ResultSet rs = null;
		Connection conn = ConnectionPool.getConn();
		String sql = "select * from students where id='" + id + "'";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			rs.next();
			stu = new students_javaBean();
			stu.setId(rs.getString("id"));
			stu.setName(rs.getString("name"));
			stu.setSex(rs.getString("sex"));
			stu.setBirth(rs.getString("birth"));
			stu.setAddress(rs.getString("address"));
			System.out.println("查询学号为'" + stu.getId() + "'基本信息完毕！");
			ConnectionPool.close(rs, stmt, conn);
			return stu;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		ConnectionPool.close(rs, stmt, conn);
		return stu;
	}

	public static boolean update(students_javaBean stu) {
		Statement stmt = null;
		int i = 0;
		Connection conn = ConnectionPool.getConn();
		try {
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_UPDATABLE);
			String sql = "update students set name='" + stu.getName()
					+ "',sex='" + stu.getSex() + "',birth='" + stu.getBirth()
					+ "',address='" + stu.getAddress() + "' where id='"
					+ stu.getId() + "' ";
			i = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		ConnectionPool.close(stmt, conn);
		if (i > 0) {
			return true;
		} else {
			return false;
		}
	}

	public void delete(String id) throws Exception {
		Connection conn = ConnectionPool.getConn();
		Statement stmt = conn.createStatement();
		stmt.execute("delete from students where id='" + id + "'");

	}

	public void add(students_javaBean stu) throws Exception {
		Connection conn = ConnectionPool.getConn();
		PreparedStatement pstmt = conn
				.prepareStatement("insert into students(id,name,sex,birth,address)"
						+ "values(?,?,?,?,?)");
		pstmt.setString(1, stu.getId());
		pstmt.setString(2, stu.getName());
		pstmt.setString(3, stu.getSex());
		pstmt.setString(4, stu.getBirth());
		pstmt.setString(5, stu.getAddress());
		pstmt.execute();

	}

}
